/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLag;

import ModelLag.Customer;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Ronni
 */
public class DbCustomer implements IFDbCustomer {
    private Connection con;
    private static final String DB_NAME = "Customer";

    public DbCustomer() {
        this.con = DbConnection1.getInstance().getDBcon();
    }
    
    @Override
    public Customer findCustomer(String phoneNo) {
        PreparedStatement preStmnt = null;    
        String query = "SELECT phoneNo, fName, lName, address, city, email, Customer.zipcode "
                         + "FROM Customer, ZipcodeCity "
                         + "WHERE phoneNo = ? AND Customer.zipCode = ZipcodeCity.zipCode";  
        //System.out.println(query);
        
        
        Customer customerObj = null;
        ResultSet results;
        try{    
            preStmnt = con.prepareStatement(query);
            preStmnt.setString(1,phoneNo);                
            results = preStmnt.executeQuery();
            if(results.next()){
                 customerObj = buildCustomer(results);	
            }
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findSpecificCustomer(): " + e.getMessage());
                
            }
            
            return customerObj;
    }

    @Override
    public ArrayList<Customer> searchCustomer(String searchString) {
        PreparedStatement preStmnt = null;          
        ArrayList<Customer> customerList = new ArrayList<Customer>();
        ResultSet results;
        Customer customerObj = null;
        try{    
        String query = "SELECT phoneNo,fName,lName,address,Customer.zipCode,email, city FROM " + DB_NAME + ",ZipCodeCity WHERE (phoneNo LIKE '%" +searchString+"%' OR fName LIKE '%" + searchString + "%' OR lName LIKE '%" + searchString + "%' OR address LIKE '%" + searchString + "%' OR Customer.zipCode LIKE '%" + searchString + "%' OR email LIKE '%" + searchString + "%') AND Customer.zipCode = ZipCodeCity.zipCode";     
        preStmnt = con.prepareStatement(query);
        results = preStmnt.executeQuery();
        while(results.next()){
            customerObj = buildCustomer(results);
            customerList.add(customerObj);	
            }
            preStmnt.close();
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception in searchCustomer: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i searchCustomer(): " + e.getMessage());
                
            }
            
            return customerList;
    }

    @Override
    public ArrayList<Customer> getAllCustomers() {
        PreparedStatement preStmnt = null;          
        ArrayList<Customer> customerList = new ArrayList<Customer>();
        ResultSet results;
        Customer customerObj = null;
        try{
            String query = "SELECT phoneNo, fName, lName, address, city, email, Customer.zipcode "
                         + "FROM Customer, ZipcodeCity "
                         + "WHERE Customer.zipCode = ZipcodeCity.zipCode";    
            preStmnt = con.prepareStatement(query);
            results = preStmnt.executeQuery();
            while(results.next()){
                 customerObj = buildCustomer(results);
                 customerList.add(customerObj);	
            }
            preStmnt.close();
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findAllCustomers(): " + e.getMessage());
                
            }
            
            return customerList;
    }

    @Override
    public int insertCustomer(Customer customer) throws SQLException {
    int rc = -1;
    PreparedStatement preStmnt = null;
    String query = null;
    try
    {
        query = "INSERT INTO " +DB_NAME+ " VALUES(?,?,?,?,?,?)";
        preStmnt = con.prepareStatement(query);
        //System.out.println(query);
        preStmnt.setString(1, customer.getPhoneNo());
        preStmnt.setString(2, customer.getfName());
        preStmnt.setString(3, customer.getlName());
        preStmnt.setString(4, customer.getAddress());
        preStmnt.setString(5, customer.getZipCode());
        preStmnt.setString(6, customer.getEmail());                
        rc = preStmnt.executeUpdate();   
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQLException in InsertCustomer " + SQLe.getMessage());
            throw new SQLException(SQLe);
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception in InsertCustomer" + e.getMessage());
        }        
        return rc;
    }

    @Override
    public int updateCustomer(Customer customer) throws SQLException{
        int rc = -1;
        String query = null;
        try
        {
           
            
//            System.out.println(customer.getPhoneNo());
            query = "UPDATE " +DB_NAME+ " SET fName = ?, lName = ?,address = ?,zipCode = ?,"
            + "email = ? WHERE phoneNo = ? ";

            PreparedStatement preStmnt = con.prepareStatement(query);
            preStmnt = con.prepareStatement(query);
            preStmnt.setString(1, customer.getfName());
            preStmnt.setString(2, customer.getlName());
            preStmnt.setString(3, customer.getAddress());
            preStmnt.setString(4, customer.getZipCode());
            preStmnt.setString(5, customer.getEmail());
            preStmnt.setString(6, customer.getPhoneNo());
            rc = preStmnt.executeUpdate();  

                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Error: " + SQLe.getMessage());
            throw new SQLException(SQLe);
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("ExceptionE: " + e.getMessage());
        }        
        return rc;
    }

    @Override
    public int deleteCustomer(Customer customer) {
        int rc = -1;
        PreparedStatement preStmnt = null;
        String query = null;
        try{
            
                query = "DELETE FROM " +DB_NAME+ " WHERE phoneNo = ?";
                preStmnt = con.prepareStatement(query);
                preStmnt.setString(1, customer.getPhoneNo());
                rc = preStmnt.executeUpdate();
                preStmnt.close();
                
        }catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Fejl: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception " + e.getMessage());
        }        
        return rc;
    }

    private Customer buildCustomer(ResultSet results) {
        Customer customerObj = new Customer();
         try{
                        
                customerObj.setPhoneNo(results.getString("phoneNo"));
                customerObj.setfName(results.getString("fName"));
                customerObj.setlName(results.getString("lName"));
                customerObj.setAddress(results.getString("address"));
                customerObj.setZipCode(results.getString("zipCode"));
                customerObj.setCity(results.getString("city"));
                customerObj.setEmail(results.getString("email"));
          }
         catch(Exception e)
         {
             System.out.println("error building customer object: " + e.getMessage());
         }
        
        return customerObj;
    }
    
}
